DROP SCHEMA IF EXISTS xlab CASCADE;

DROP USER IF EXISTS xlab;

CREATE USER xlab
WITH LOGIN
ENCRYPTED PASSWORD 'xlab'
NOINHERIT
VALID UNTIL 'infinity';

CREATE SCHEMA xlab AUTHORIZATION xlab;

CREATE TABLE xlab.types (
	type_id INTEGER,
	type_name VARCHAR(32),
	title VARCHAR(255),
	not_instantiable SMALLINT,
	not_inheritable SMALLINT,
	parent_type_id INTEGER
)
WITHOUT OIDS;

ALTER TABLE xlab.types OWNER TO xlab;

ALTER TABLE xlab.types
ALTER COLUMN type_name SET NOT NULL;

ALTER TABLE xlab.types
ALTER COLUMN not_instantiable SET NOT NULL;

ALTER TABLE xlab.types
ALTER COLUMN not_inheritable SET NOT NULL;

ALTER TABLE xlab.types
ADD CONSTRAINT types_pk PRIMARY KEY (type_id);

ALTER TABLE xlab.types
ADD CONSTRAINT types_name_uk UNIQUE (type_name);

ALTER TABLE xlab.types
ADD CONSTRAINT types_not_instantiable_ck CHECK (not_instantiable IN (0, 1));

ALTER TABLE xlab.types
ADD CONSTRAINT types_not_inheritable_ck CHECK (not_inheritable IN (0, 1));

ALTER TABLE xlab.types
ADD CONSTRAINT types_parent_type_fk FOREIGN KEY (parent_type_id)
REFERENCES xlab.types (type_id)
ON DELETE SET NULL;

ALTER TABLE xlab.types
ALTER COLUMN not_instantiable SET DEFAULT 0;

ALTER TABLE xlab.types
ALTER COLUMN not_inheritable SET DEFAULT 0;

CREATE SEQUENCE xlab.types_id_seq
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
START WITH 1
NO CYCLE
OWNED BY NONE;

ALTER TABLE xlab.types_id_seq OWNER TO xlab;

CREATE OR REPLACE FUNCTION xlab.types_tg_func()
RETURNS TRIGGER AS $types_tg_func$
BEGIN
	IF NEW.type_id IS NULL THEN
		NEW.type_id := NEXTVAL('xlab.types_id_seq');
	END IF;

	RETURN NEW;
END;
$types_tg_func$ LANGUAGE plpgsql;

ALTER FUNCTION xlab.types_tg_func() OWNER TO xlab;

CREATE TRIGGER types_tg
BEFORE INSERT ON xlab.types
FOR EACH ROW
EXECUTE PROCEDURE xlab.types_tg_func();
